import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.lang.String;
import java.sql.ResultSet;


@WebServlet(urlPatterns = {"/jdbc1"})
public class Jdbcwork extends HttpServlet {
    @Override
    // curl   http://localhost:8080/test/jdbc1

    protected void doGet(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException {

        java.sql.Connection conn = null;

        java.sql.Statement sqlStmt = null; //语句对象
        java.sql.ResultSet sqlRst = null; //结果集对象

        try {


           conn = java.sql.DriverManager.getConnection("jdbc:h2:mem:test_mem;MODE=MYSQL;DB_CLOSE_DELAY=-1", "sa", "");

            sqlStmt = conn.createStatement(java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE, java.sql.ResultSet.CONCUR_READ_ONLY);

           String sqlQuery = "CREATE TABLE `student` (\n" +

                    "                `studentno` varchar(100) NOT NULL,\n" +
                    "                `course` varchar(100) NOT NULL,\n" +
                    "                `score` int(10) NOT NULL\n" +
                    "            )";
            sqlStmt.executeUpdate(sqlQuery);


            sqlStmt.executeUpdate("insert into `student` (`studentno`,`course`,`score`) values ('201741412102','Python',85)");
            sqlStmt.executeUpdate("insert into `student` (`studentno`,`course`,`score`) values ('201741412104','JavaEE',75)");
            sqlStmt.executeUpdate("insert into `student` (`studentno`,`course`,`score`) values ('201741412103','PHP',95)");

            ResultSet s = sqlStmt.executeQuery("select * from student");


            while (s.next()) {
                res.getWriter().println(s.getString("studentno") + "," + s.getString("course") + "," + s.getInt("score"));
            }

    }catch (java.sql.SQLException e) {
            e.printStackTrace();
    }
    }
}
